package com.wiserp.rest.helper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.wiserp.rest.model.OpResult;
import com.wiserp.rest.model.WoInv;
import com.wiserp.rest.model.WoInvItem;

public class WoInvHelper extends JdbcDaoSupport implements INounHelper {
	private static final String SQL_GET_UNPOSTED_WOINV = "SELECT hbd001,hbd005 FROM shb04 WHERE hbd006='4' AND hbd003='4g' AND hbd100='a'";	
	private static final String SQL_GET_UNPOSTED_COUNT_WOINV = "SELECT COUNT(*) FROM shb04 WHERE hbd006='4' AND hbd003='4g' AND hbd100='a'";	
	private static final String SQL_GET_WOINVITEM_BY_FLOWNO = "SELECT a.hbe001,a.hbe004,(b.kaa007-b.kaa009) qty,a.hbe006,a.hbe017 FROM shb05 a LEFT JOIN ska01 b ON(a.hbe006=b.kaa001) WHERE a.hbe002=?";
	private static final String SQL_GET_WOINV_BY_FLOWNO = "SELECT hbd001,hbd005 FROM shb04 WHERE hbd001=? AND hbd006='4' AND hbd100='a'";
	private static final String SQL_UPDATE_WOINVITEM = "UPDATE shb05 SET hbe019=?,hbe012=?,hbe017=?,hbe003=?,hbe015=hbe014*?,hbe026=hbe025*? WHERE hbe001=? AND hbe002=?";

	private PlatformTransactionManager txManager;
	public void setTxManager(PlatformTransactionManager txManager) {
		this.txManager = txManager;
	}	
	/**
	 * 获取所有未过账的工单入库单 hbd003: 
	 * 	4g 工单入库  4h 工单退库
	 * 过账状态 hbd006:
	 * 	u 已过账 4未过账
	 */
	public List findUnpost() {
		return this.getJdbcTemplate().query(SQL_GET_UNPOSTED_WOINV,
				new RowMapper<WoInv>() {
					public WoInv mapRow(ResultSet rs, int i)
							throws SQLException {
						WoInv data = new WoInv();
						data.setFlowNo(rs.getString("hbd001"));
						data.setBillDate(rs.getDate("hbd005"));
						return data;
					}
				});
	}
	
	public Object getByFlowNo(String flowNo) {
		Object result = null;
		try{
			result = this.getJdbcTemplate().queryForObject(SQL_GET_WOINV_BY_FLOWNO,
				new RowMapper<WoInv>() {
					public WoInv mapRow(ResultSet rs, int i)
							throws SQLException {
						WoInv data = new WoInv();
						data.setFlowNo(rs.getString("hbd001"));
						data.setBillDate(rs.getDate("hbd005"));
						data.setItems(findItems(data.getFlowNo()));
						return data;
					}
				}, flowNo);
		}
		catch(EmptyResultDataAccessException e){ 
			result = OpResult.FAIL(String.format("单据(%s)不存在或者已过账或者已上传！", flowNo), 1);
		}
		return result;
	}
	
	public List findItems(String flowNo){
		return this.getJdbcTemplate().query(SQL_GET_WOINVITEM_BY_FLOWNO,
				new RowMapper<WoInvItem>() {
					public WoInvItem mapRow(ResultSet rs, int i)
							throws SQLException {
						WoInvItem data = new WoInvItem();
						data.setItemNo(rs.getInt("hbe001"));
						data.setProdNo(rs.getString("hbe004"));
						data.setQty(rs.getDouble("qty"));
						data.setWoFlowNo(rs.getString("hbe006"));
						data.setBatchNo(rs.getString("hbe017"));
						return data;
					}
				}, flowNo);
	}
	
	public OpResult updateInfo(final Object bill){
		OpResult result = null;
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		TransactionStatus status = txManager.getTransaction(def);
		try{
			result = updateInfoInternal(bill);
			txManager.commit(status);
		}catch(Exception ex){
			ex.printStackTrace();
			txManager.rollback(status);
			result = OpResult.UNKNOWNFAIL;
		}
		return result;
	}	
	
	public OpResult updateInfoInternal(final Object bill){
		WoInv data = (WoInv)bill;
		Object result = getByFlowNo(data.getFlowNo());
		if(result instanceof OpResult)
			return (OpResult)result;
		WoInv dataDB = (WoInv)result;
		StringBuffer msg = new StringBuffer("");
		for(WoInvItem item:data.getItems()){
			boolean found = false;
			for(WoInvItem itemDB:dataDB.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					found = true;
					break;
				}
			}
			
			if(!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目在ERP中不存在", item.getItemNo(),item.getProdNo()));
		}
		
		if(msg.length()>0){
			msg.insert(0, String.format("上传的单据(%s)中", data.getFlowNo())).append(",请检查");
			return OpResult.FAIL(msg.toString(), 2);
		}
		
		for(WoInvItem itemDB:dataDB.getItems()){
			boolean found = false;
			for(WoInvItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					found = true;
					break;
				}
			}
			
			if(!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目没有更新", itemDB.getItemNo(),itemDB.getProdNo()));
		}
		
		if(msg.length()>0){
			msg.insert(0, String.format("ERP的单据(%s)中", dataDB.getFlowNo())).append(",请检查");
			return OpResult.FAIL(msg.toString(), 3);
		}
		
		for(WoInvItem itemDB:dataDB.getItems()){
			double inQtyTotal = 0.0;
			for(WoInvItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo()))
					inQtyTotal += item.getInQty();
			}
			
			if(inQtyTotal > itemDB.getQty())
				msg.append(String.format(
						",项目编号(%d)物料编号为(%s)的项目的入库数量大于可入库数量",
						itemDB.getItemNo(), itemDB.getProdNo()));
		}

		
		if(msg.length()>0){
			msg.insert(0, String.format("上传的单据(%s)中", dataDB.getFlowNo())).append(",请检查");
			return OpResult.FAIL(msg.toString(), 4);
		}
		
		if(StringUtils.isNotEmpty(data.getOperatorNo()))
			this.getJdbcTemplate().update("UPDATE shb04 SET hbd012=?,hbd100='1' WHERE hbd001=?",data.getOperatorNo(),data.getFlowNo());
		else
			this.getJdbcTemplate().update("UPDATE shb04 SET hbd100='1' WHERE hbd001=?",data.getFlowNo());
		
		for(WoInvItem itemDB:dataDB.getItems()){
			boolean updated = false;
			for(WoInvItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					String parentWhNo = getParentWhNo(item.getWhLocNo());
					if(!updated){
						this.getJdbcTemplate().update(SQL_UPDATE_WOINVITEM, 
								parentWhNo,item.getInQty(),item.getBatchNo(),item.getWhLocNo(),item.getInQty(),item.getInQty(),item.getItemNo(),data.getFlowNo());
						updated = true;
					}else{
						copyInsert(data,item,parentWhNo);
					}
				}
					
			}
		}
		
		return OpResult.SUCCESS;
	}

	public int getUnpostCount() {
		return this.getJdbcTemplate().queryForInt(
				SQL_GET_UNPOSTED_COUNT_WOINV);
	}
	
	
	private String getParentWhNo(String whLocNo){
		List<String> l =this.getJdbcTemplate().queryForList("SELECT haa012 FROM sha01 WHERE haa001=?", String.class,whLocNo);
		if(l!=null && l.size()>0)
			return l.get(0);
		else
			return "";
	}
	
	
	private void copyInsert(WoInv data,WoInvItem item,String parentWhNo){
		int maxNo = this.getJdbcTemplate().queryForInt("SELECT MAX(hbe001) FROM shb05 WHERE hbe002=?",data.getFlowNo());
		int nextNo = maxNo+10;
		this.getJdbcTemplate().update(
				"INSERT INTO shb05(hbe001,hbe002,hbe003,hbe004,hbe005,hbe006,hbe007,hbe008,hbe009,hbe010," +
				"hbe011,hbe012,hbe013,hbe014,hbe015,hbe016,hbe017,hbe018,hbe019,hbe020," +
				"hbe021,hbe022,hbe023,hbe024,hbe025,hbe026,hbe027,hbe028,hbe029,hbe030) " +
				"SELECT ?,hbe002,?,hbe004,hbe005,hbe006,hbe007,hbe008,hbe009,hbe010," +
				"hbe011,?,hbe013,hbe014,hbe014*?,hbe016,?,hbe018,?,hbe020," +
				"hbe021,hbe022,hbe023,hbe024,hbe025,hbe025*?,hbe027,hbe028,hbe029,hbe030 " +
				"FROM shb05 WHERE hbe001=? AND hbe002=?",
				nextNo,item.getWhLocNo(),item.getInQty(),item.getInQty(),item.getBatchNo(),parentWhNo,item.getInQty(),item.getItemNo(),data.getFlowNo());
	}
}
